package tr.com.argela.tap.mot.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import tr.com.argela.tap.mot.logger.LoggerFactory;
import tr.com.argela.tap.mot.models.Module;
import tr.com.argela.tap.mot.models.Project;
import tr.com.argela.tap.mot.models.User;

/**
 * @author arge
 */
public class ModuleDao {

    private static Logger logger = LoggerFactory.getLogger("servlets");

    public ModuleDao() {
    }

    public List<Module> seemodule(Long project_id) {
        Module module = null;
        PreparedStatement stmt = null;
        List<Module> modulelist = new ArrayList<Module>();
        String sql = "select * from module where project_id= ? ";

        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = new DbManager();
        try {
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, project_id);
            rs = stmt.executeQuery();

            while (rs.next()) {
                module = new Module();
                module.setId(rs.getLong("id"));
                module.setName(rs.getString("name"));

                User user = new User();
                user.setId(rs.getLong("user_id"));
                module.setUser(user);
                module.setDescription(rs.getString("description"));
                Project project = new Project();
                project.setId(rs.getLong("project_id"));
                module.setProject(project);
                modulelist.add(module);
            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (module == null) {
            System.out.println("Module is null: ");
        }
        return modulelist;
    }

    public List<Module> seemodule2(Long user_id) {
        Module module = null;
        PreparedStatement stmt = null;
        List<Module> modulelist = new ArrayList<Module>();
        String sql = "select * from module where user_id= ? ";
        DbManager dbManager = new DbManager();
        System.out.println("Query: " + sql);
        ResultSet rs;
        try {
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, user_id);
            rs = stmt.executeQuery();

            while (rs.next()) {
                module = new Module();
                module.setId(rs.getLong("id"));
                module.setName(rs.getString("name"));

                User user = new User();
                user.setId(rs.getLong("user_id"));
                module.setUser(user);
                module.setDescription(rs.getString("description"));
                Project project = new Project();
                project.setId(rs.getLong("project_id"));
                module.setProject(project);
                modulelist.add(module);
            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (module == null) {
            System.out.println("Module is null: ");
        }
        return modulelist;
    }

    public Module checkmodule(Long id) {
        Module module = null;
        PreparedStatement stmt = null;
        String sql = "select m.id as module_id, m.name as module_name, m.description,u.name as username, "
                + "u.id as user_id, p.name as pr_name, p.id as pr_id from module m, user u,project p where m.id = ? and u.id = m.user_id and p.id = m.project_id";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = new DbManager();
        try {
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, id);
            rs = stmt.executeQuery();
            module = new Module();
            while (rs.next()) {
                module.setId(rs.getLong("module_id"));
                module.setName(rs.getString("module_name"));

                User user = new User();
                user.setName(rs.getString("username"));
                user.setId(rs.getLong("user_id"));
                module.setUser(user);
                module.setDescription(rs.getString("description"));
                Project project = new Project();
                project.setName(rs.getString("pr_name"));
                project.setId(rs.getLong("pr_id"));
                module.setProject(project);
            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (module == null) {
            System.out.println("module is null: ");
        }
        return module;

    }

    public Module checkmodule2() {
        Module module = null;
        PreparedStatement stmt = null;
        String sql = "select * from module";
        ResultSet rs;
        DbManager dbManager = new DbManager();
        try {
            stmt = dbManager.getConn().prepareStatement(sql);

            rs = stmt.executeQuery();
            module = new Module();
            while (rs.next()) {
                module.setId(rs.getLong("id"));
                module.setName(rs.getString("name"));

                User user = new User();

                user.setId(rs.getLong("user_id"));
                module.setUser(user);
                module.setDescription(rs.getString("description"));
                Project project = new Project();

                project.setId(rs.getLong("project_id"));
                module.setProject(project);
            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (module == null) {
            System.out.println("module is null: ");
        }
        return module;

    }

    public List<Module> seemodulelist() {
        Module module = null;
        PreparedStatement stmt = null;
        List<Module> modulelist = new ArrayList<Module>();
        String sql = "select * from module";
        DbManager dbManager = new DbManager();
        System.out.println("Query: " + sql);
        ResultSet rs;
        try {
            stmt = dbManager.getConn().prepareStatement(sql);

            rs = stmt.executeQuery();

            while (rs.next()) {
                module = new Module();
                module.setId(rs.getLong("id"));
                module.setName(rs.getString("name"));

                User user = new User();
                user.setId(rs.getLong("user_id"));
                module.setUser(user);
                module.setDescription(rs.getString("description"));
                Project project = new Project();
                project.setId(rs.getLong("project_id"));
                module.setProject(project);
                modulelist.add(module);
            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (module == null) {
            System.out.println("Project is null: ");
        }
        return modulelist;
    }

    public int addModule(DbManager dbManager, String name, User user, String desc, Project project) {
        Module module = null;
        PreparedStatement stmt = null;
        int effectedRows = 0;

        String sql = "insert into module (name,user_id,description,project_id) values (?,?,?,?)";
        System.out.println("Query: " + sql);
        try {
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setString(1, name);
            stmt.setLong(2, user.getId());
            stmt.setString(3, desc);
            stmt.setLong(4, project.getId());
            effectedRows = stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

        }

        if (module == null) {
            System.out.println("Module is null: ");
        }
        return effectedRows;
    }

    public int UpdateModule(DbManager dbManager, Long moduleId, String name, User user, String desc, Project project) {

        PreparedStatement stmt = null;
        int effectedRows = 0;

        String sql = "update module set  name = ?, user_id = ?, description = ?, project_id = ? where id = ? ";
        System.out.println("Query: " + sql);
        try {
            stmt = dbManager.getConn().prepareStatement(sql);

            stmt.setString(1, name);
            stmt.setLong(2, user.getId());
            stmt.setString(3, desc);
            stmt.setLong(4, project.getId());
            stmt.setLong(5, moduleId);
            effectedRows = stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }

        return effectedRows;
    }

    public Module getModuleById(Long id) {
        Module module = null;
        PreparedStatement stmt = null;
        String sql = "select * from module where id = ? ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = new DbManager();
        try {
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, id);
            rs = stmt.executeQuery();
            module = new Module();
            while (rs.next()) {
                module.setId(rs.getLong("id"));
                module.setName(rs.getString("name"));

                User user = new User();
                user.setId(rs.getLong("user_id"));
                module.setUser(user);
                module.setDescription(rs.getString("description"));
                Project project = new Project();
                project.setId(rs.getLong("project_id"));
                module.setProject(project);
            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (module == null) {
            System.out.println("module is null: ");
        }
        return module;

    }

}